package com.ics.atable.chat.service;

import com.ics.atable.chat.config.DynamicDataSourceManager;
import com.ics.atable.chat.model.dto.SqlExecuteRequest;
import com.ics.atable.chat.model.dto.SqlExecuteResult;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.List;
import java.util.Map;

/**
 * 动态SQL执行服务
 * 
 * 负责执行动态SQL语句，支持SELECT、INSERT、UPDATE、DELETE四种操作
 * 提供分页查询、参数绑定、SQL注入防护等功能
 * 
 * @author Chawu
 */
@Slf4j
@Service
public class DynamicSqlExecutorService {
    
    @Autowired
    private DynamicDataSourceManager dynamicDataSourceManager;
    
    /**
     * 执行SQL语句
     * 
     * @param request SQL执行请求
     * @return 执行结果
     */
    public SqlExecuteResult executeSql(SqlExecuteRequest request) {
        long startTime = System.currentTimeMillis();
        
        try {
            // 参数验证
            validateRequest(request);
            
            // 获取JdbcTemplate
            JdbcTemplate jdbcTemplate = dynamicDataSourceManager.getJdbcTemplate(request.getDataSourceName());
            if (jdbcTemplate == null) {
                return SqlExecuteResult.error("数据源不存在: " + request.getDataSourceName());
            }
            
            // 解析SQL类型
            SqlType sqlType = parseSqlType(request.getSql());
            log.info("执行SQL类型: {}, 数据源: {}", sqlType, request.getDataSourceName());
            
            // 根据SQL类型执行不同的操作
            SqlExecuteResult result;
            switch (sqlType) {
                case SELECT:
                    result = executeSelect(jdbcTemplate, request);
                    break;
                case INSERT:
                case UPDATE:
                case DELETE:
                    result = executeUpdate(jdbcTemplate, request);
                    break;
                default:
                    result = SqlExecuteResult.error("不支持的SQL类型");
            }
            
            // 设置执行时间
            long executionTime = System.currentTimeMillis() - startTime;
            result.setExecutionTime(executionTime);
            
            log.info("SQL执行完成，耗时: {}ms", executionTime);
            return result;
            
        } catch (Exception e) {
            log.error("执行SQL时发生错误: {}", e.getMessage(), e);
            return SqlExecuteResult.error("执行SQL失败: " + e.getMessage());
        }
    }
    
    /**
     * 执行SELECT查询
     * 
     * @param jdbcTemplate JdbcTemplate对象
     * @param request 执行请求
     * @return 查询结果
     */
    private SqlExecuteResult executeSelect(JdbcTemplate jdbcTemplate, SqlExecuteRequest request) {
        try {
            String sql = request.getSql();
            List<Object> params = request.getParams();
            
            // 检查是否需要分页
            if (request.getPageNum() != null && request.getPageSize() != null && 
                request.getPageNum() > 0 && request.getPageSize() > 0) {
                
                return executeSelectWithPagination(jdbcTemplate, sql, params, 
                    request.getPageNum(), request.getPageSize());
            } else {
                // 不分页查询
                List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, 
                    params != null ? params.toArray() : new Object[0]);
                return SqlExecuteResult.success(data);
            }
            
        } catch (Exception e) {
            log.error("执行SELECT查询失败: {}", e.getMessage(), e);
            return SqlExecuteResult.error("查询失败: " + e.getMessage());
        }
    }
    
    /**
     * 执行分页查询
     * 
     * @param jdbcTemplate JdbcTemplate对象
     * @param sql SQL语句
     * @param params 参数
     * @param pageNum 页码
     * @param pageSize 每页大小
     * @return 分页查询结果
     */
    private SqlExecuteResult executeSelectWithPagination(JdbcTemplate jdbcTemplate, 
                                                        String sql, List<Object> params, 
                                                        Integer pageNum, Integer pageSize) {
        try {
            // 计算分页参数
            int offset = (pageNum - 1) * pageSize;
            
            // 构建分页SQL（MySQL语法）
            String countSql = "SELECT COUNT(*) FROM (" + sql + ") AS count_table";
            String pageSql = sql + " LIMIT " + offset + ", " + pageSize;
            
            // 执行总数查询
            Long total = jdbcTemplate.queryForObject(countSql, Long.class, 
                params != null ? params.toArray() : new Object[0]);
            
            // 执行分页查询
            List<Map<String, Object>> data = jdbcTemplate.queryForList(pageSql, 
                params != null ? params.toArray() : new Object[0]);
            
            return SqlExecuteResult.success(data, total, pageNum, pageSize);
            
        } catch (Exception e) {
            log.error("执行分页查询失败: {}", e.getMessage(), e);
            return SqlExecuteResult.error("分页查询失败: " + e.getMessage());
        }
    }
    
    /**
     * 执行INSERT、UPDATE、DELETE操作
     * 
     * @param jdbcTemplate JdbcTemplate对象
     * @param request 执行请求
     * @return 执行结果
     */
    private SqlExecuteResult executeUpdate(JdbcTemplate jdbcTemplate, SqlExecuteRequest request) {
        try {
            String sql = request.getSql();
            List<Object> params = request.getParams();
            
            // 执行更新操作
            int affectedRows = jdbcTemplate.update(sql, 
                params != null ? params.toArray() : new Object[0]);
            
            log.info("SQL执行成功，影响行数: {}", affectedRows);
            return SqlExecuteResult.success(affectedRows);
            
        } catch (Exception e) {
            log.error("执行UPDATE/DELETE/INSERT失败: {}", e.getMessage(), e);
            return SqlExecuteResult.error("执行失败: " + e.getMessage());
        }
    }
    
    /**
     * 解析SQL类型
     * 
     * @param sql SQL语句
     * @return SQL类型
     */
    private SqlType parseSqlType(String sql) {
        try {
            // 使用JSQLParser解析SQL
            Statement statement = CCJSqlParserUtil.parse(sql);
            
            if (statement instanceof Select) {
                return SqlType.SELECT;
            } else if (statement instanceof Insert) {
                return SqlType.INSERT;
            } else if (statement instanceof Update) {
                return SqlType.UPDATE;
            } else if (statement instanceof Delete) {
                return SqlType.DELETE;
            } else {
                return SqlType.UNKNOWN;
            }
            
        } catch (JSQLParserException e) {
            log.warn("SQL解析失败，使用简单匹配: {}", e.getMessage());
            
            // 如果解析失败，使用简单的字符串匹配
            String upperSql = sql.trim().toUpperCase();
            if (upperSql.startsWith("SELECT")) {
                return SqlType.SELECT;
            } else if (upperSql.startsWith("INSERT")) {
                return SqlType.INSERT;
            } else if (upperSql.startsWith("UPDATE")) {
                return SqlType.UPDATE;
            } else if (upperSql.startsWith("DELETE")) {
                return SqlType.DELETE;
            } else {
                return SqlType.UNKNOWN;
            }
        }
    }
    
    /**
     * 验证请求参数
     * 
     * @param request 执行请求
     */
    private void validateRequest(SqlExecuteRequest request) {
        if (request == null) {
            throw new IllegalArgumentException("请求参数不能为空");
        }
        
        if (!StringUtils.hasText(request.getDataSourceName())) {
            throw new IllegalArgumentException("数据源名称不能为空");
        }
        
        if (!StringUtils.hasText(request.getSql())) {
            throw new IllegalArgumentException("SQL语句不能为空");
        }
        
        // 检查数据源是否存在
        if (!dynamicDataSourceManager.containsDataSource(request.getDataSourceName())) {
            throw new IllegalArgumentException("数据源不存在: " + request.getDataSourceName());
        }
    }
    
    /**
     * SQL类型枚举
     */
    private enum SqlType {
        SELECT,     // 查询
        INSERT,     // 插入
        UPDATE,     // 更新
        DELETE,     // 删除
        UNKNOWN     // 未知类型
    }
}
